package com.ruoyi.gen.util;

import cn.hutool.core.date.DateTime;
import com.common.zrd.mysql.MySqlInfo;
import com.ruoyi.gen.domain.GenTable;
import com.ruoyi.gen.domain.GenTableColumn;
import com.ruoyi.gen.domain.GeneratorDataSource;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author: zrd
 * @Date: 2020/4/24 09:07
 * @Description:
 */


public class MysqlGenUtils {

    private Statement stmt;
    private String database;
    private String generatedSql = "";
    private String sqlFileName = "";
    private String zipFileName = "";
    private File generatedZipFile;
    private MySqlInfo mySqlInfo = null;


    public MysqlGenUtils(MySqlInfo mySqlInfo) {
        this.mySqlInfo = mySqlInfo;
    }

    /**
     * 通过jdbcurl连接数据库
     *
     * @param username   database username
     * @param password   database password
     * @param jdbcURL    the user supplied JDBC URL. It's used as is. So ensure you supply the right parameters
     * @param driverName the user supplied mysql connector driver class name
     * @return Connection
     * @throws ClassNotFoundException exception
     * @throws SQLException           exception
     */
    static Connection connectWithURL(String username, String password, String jdbcURL, String driverName) throws ClassNotFoundException, SQLException {
        String driver = (Objects.isNull(driverName) || driverName.isEmpty()) ? "com.mysql.cj.jdbc.Driver" : driverName;
        return doConnect(driver, jdbcURL, username, password);
    }

    /**
     * 连接数据库
     *
     * @param driver   the class name for the mysql driver to use
     * @param url      the url of the database
     * @param username database username
     * @param password database password
     * @return Connection
     * @throws SQLException           exception
     * @throws ClassNotFoundException exception
     */
    private static Connection doConnect(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println("DB Connected Successfully" );
        return connection;
    }

    /**
     * 描述： 设置数据源
     * 备注：
     * 日期： 14:45 2020/12/25
     * 作者： zrd
     *
     * @param generatorDataSource
     * @return com.common.zrd.mysql.MySqlInfo
     **/
    public static MySqlInfo setMySqlInfo(GeneratorDataSource generatorDataSource) {
        List<GenTable> list = new ArrayList<>();
        MySqlInfo mySqlInfo = new MySqlInfo();
        mySqlInfo.setJdbcUrl(generatorDataSource.getUrl());
        mySqlInfo.setUser(generatorDataSource.getDbUser());
        mySqlInfo.setPassword(generatorDataSource.getDbPassword());
        mySqlInfo.setExportPath("D:\\");

        return mySqlInfo;
    }

    /**
     * 从jdbcUrl中提取数据库名
     *
     * @param jdbcUrl
     * @return
     */
    private String findDBName(String jdbcUrl) {
        String dbName = "";
        int s = jdbcUrl.lastIndexOf("/") + 1;
        int e = jdbcUrl.lastIndexOf("?");
        dbName = jdbcUrl.substring(s, e);
        return dbName;
    }

    /**
     * 描述：传入sql语句获取返回结果
     * 备注：
     * 日期： 16:05 2020/12/18
     * 作者： zrd
     *
     * @param sql
     * @return java.sql.ResultSet
     **/
    public List<GenTable> getTablesInfo(String sql) throws IOException, SQLException, ClassNotFoundException {
        List<GenTable> list = new ArrayList<>();
        //connect to the database
        database = findDBName(this.mySqlInfo.getJdbcUrl());
        String driverName = "";

        Connection connection;

        connection = connectWithURL(this.mySqlInfo.getUser(),
                this.mySqlInfo.getPassword(), this.mySqlInfo.getJdbcUrl(),
                driverName
        );

        stmt = connection.createStatement();
//        PreparedStatement ps = conn.preparedStatement(sql.toString());
        ResultSet resultSet;
        resultSet = stmt.executeQuery(sql);
        while (resultSet.next()) {
            GenTable table = new GenTable();
            table.setTableName(resultSet.getString(1));
            table.setTableComment(resultSet.getString(2));
            table.setCreateTime(DateTime.of(resultSet.getTimestamp(3)));
            if (resultSet.getTimestamp(4) != null) {
                table.setUpdateTime(DateTime.of(resultSet.getTimestamp(4)));
            }

            list.add(table);
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return list;
    }

    /**
     * 描述：获取列 属性
     * 备注：
     * 日期： 17:00 2020/12/25
     * 作者： zrd
     *
     * @param tableName
     * @param dbName
     * @return java.util.List<com.ruoyi.gen.domain.GenTableColumn>
     **/
    public List<GenTableColumn> getGenTableColumn(String tableName, String dbName) throws IOException, SQLException,
            ClassNotFoundException {
        List<GenTableColumn> list = new ArrayList<>();
        //connect to the database
        database = findDBName(this.mySqlInfo.getJdbcUrl());
        String driverName = "";
        Connection connection;
        connection = connectWithURL(this.mySqlInfo.getUser(),
                this.mySqlInfo.getPassword(), this.mySqlInfo.getJdbcUrl(),
                driverName
        );
        stmt = connection.createStatement();
        ResultSet resultSet;
        String sql1 = "select column_name, (case when (is_nullable = 'no' &&  column_key != 'PRI') then " +
                "'1' else null end) as is_required, (case when column_key = 'PRI' then '1' else '0' end) as is_pk, " +
                "ordinal_position as sort, column_comment, (case when extra = 'auto_increment' then '1' else '0' end)" +
                " as is_increment, column_type" +
                " from information_schema.columns where table_schema = '" + dbName + "' and table_name = '" + tableName +
                "' order by ordinal_position";
        resultSet = stmt.executeQuery(sql1);
        while (resultSet.next()) {
            GenTableColumn table = new GenTableColumn();
            table.setColumnName(resultSet.getString(1));
            table.setColumnComment(resultSet.getString(5));
            table.setIsPk(resultSet.getString(3));
            table.setSort(Integer.valueOf(resultSet.getString(4)));
            table.setIsIncrement(resultSet.getString(6));
            table.setIsRequired(resultSet.getString(2));
            table.setColumnType(resultSet.getString(7));


            list.add(table);
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return list;
    }

}